package com.vinhvt.effiport.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.vinhvt.effiport.financial.ep.Asset;
import com.vinhvt.effiport.financial.ep.Organization;
import com.vinhvt.effiport.financial.ep.Security;
import com.vinhvt.effiport.financial.ep.Stock;
import com.vinhvt.effiport.financial.ep.Transaction;

class DefaultDataProvider implements DataProvider {	
	private static final String CREATE_ORGANIZATION = "INSERT INTO organization(symbol, name, listing_volume, outstanding_volume, ipo_date) VALUES(?,?,?,?,?)";
	
	private static final String CREATE_TRANSACTION = "INSERT INTO transaction(symbol) values(?)";
	private static final String GET_TRANSACTION = "SELECT * FROM transaction WHERE symbol = ? ORDER BY symbol, transaction_date";	
	private static final String GET_TRANSACTION_IN_PERIOD = "SELECT * FROM transaction WHERE symbol = ? AND (transaction_date BETWEEN ? AND ?) ORDER BY symbol, transaction_date";
	private static final String GET_STOCK_LIST = "SELECT symbol FROM transaction  t " +
													"WHERE (t.transaction_date between '1/SEP/2008' AND '1/SEP/2009') " +
													"GROUP BY symbol HAVING count(symbol) = 251 ORDER BY symbol";
	private static final String GET_CLOSED_PRICE = "SELECT closed_price FROM transaction WHERE symbol = ?";
													
	private ConnectionManager manager;
	
	public DefaultDataProvider(ConnectionManager manager){
		this.manager = manager;
	}	

	@Override
	public void createOrganization(Organization org) {		
		Connection connection = null;
		PreparedStatement pstmt = null;

		try {			
			connection = manager.getConnection();			
			
			pstmt = connection.prepareStatement(CREATE_ORGANIZATION);
			pstmt.setString(1, org.getSymbol());
			pstmt.setString(2, org.getName());
			pstmt.setLong(3, org.getListingVolume());
			pstmt.setLong(4,org.getOutstingVolume());			
			pstmt.setDate(5, new java.sql.Date(org.getIpoDate().getTime()));						
			
			pstmt.execute();
			manager.closeManager();
		} catch (Exception e) {			
			e.printStackTrace();			
		}
		finally {
			manager.close(pstmt);
		}
	}

	@Override
	public void createTransaction(Transaction tran) {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement pstmt = null;

		try {			
			connection = manager.getConnection();			
			
			pstmt = connection.prepareStatement(CREATE_TRANSACTION);
			pstmt.setString(1, tran.getTicker());
			pstmt.setDate(2, new java.sql.Date(tran.getDate().getTime()));
			pstmt.setDouble(3, tran.getOpenedPrice());
			pstmt.setDouble(4,tran.getHighestPrice());
			pstmt.setDouble(5, tran.getLowestPrice());
			pstmt.setDouble(6, tran.getClosedPrice());
			pstmt.setLong(7,tran.getVolume());
							
			pstmt.execute();
			manager.closeManager();
		} catch (Exception e) {			
			e.printStackTrace();			
		}
		finally {
			manager.close(pstmt);
		}
	}

	@Override
	public List<Transaction> getTransaction(String symbol) {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Transaction> trans = new ArrayList<Transaction>();
		try {
			connection = manager.getConnection();
			
			pstmt = connection.prepareStatement(GET_TRANSACTION);
			pstmt.setString(1, symbol);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				String ticker = rs.getString(1);
				Date date = rs.getDate(2);
//				double openedPrice = rs.getDouble(3);
//				double highestPrice = rs.getDouble(4);
//				double lowestPrice = rs.getDouble(5);
				double closedPrice = rs.getDouble(6);
				long volume = rs.getLong(7);
				Stock stock = new Stock(ticker);
				
				Transaction tran = new Transaction(stock, date, closedPrice, volume);
				trans.add(tran);
			}						
		} catch (Exception e) {
			e.printStackTrace();
		}	
		finally {
			manager.close(pstmt);
		}
		return trans;
	}
	


	@Override
	public List<Transaction> getTransaction(String symbol, Date fromDate, Date toDate) {
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Transaction> trans = new ArrayList<Transaction>();
		try {
			connection = manager.getConnection();
			
			pstmt = connection.prepareStatement(GET_TRANSACTION_IN_PERIOD);
			pstmt.setString(1, symbol);
			pstmt.setDate(2, new java.sql.Date(fromDate.getTime()));
			pstmt.setDate(3, new java.sql.Date(toDate.getTime()));
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				String ticker = rs.getString(1);
				Date date = rs.getDate(2);
//				double openedPrice = rs.getDouble(3);
//				double highestPrice = rs.getDouble(4);
//				double lowestPrice = rs.getDouble(5);
				double closedPrice = rs.getDouble(6);
				long volume = rs.getLong(7);
				Stock stock = new Stock(ticker);
				
				Transaction tran = new Transaction(stock, date, closedPrice, volume);
				trans.add(tran);
			}						
		} catch (Exception e) {
			e.printStackTrace();
		}	
		finally {
			manager.close(pstmt);
		}
		return trans;
	}

	@Override
	public List<String> getStockList() {
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<String> list = new ArrayList<String>();
		try {
			connection = manager.getConnection();
			pstmt = connection.prepareStatement(GET_STOCK_LIST);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				String symbol = rs.getString(1);
				list.add(symbol);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			manager.close(pstmt);
		}
		
		return list;
	}

	@Override
	public List<Double> getClosedPrice(String symbol) {
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Double> result = new ArrayList<Double>();
		try {
			connection = manager.getConnection();
			pstmt = connection.prepareStatement(GET_CLOSED_PRICE);
			pstmt.setString(1, symbol);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				double price = rs.getDouble(1);
				result.add(price);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			manager.close(pstmt);
		}
		
		return result;
	}
	
	
}
